package core;

import java.sql.*;
import java.util.ArrayList;

import protocal.*;

/**
 * 
 * @author chenhaipeng
 *
 */
public class GimDatabase {

	private Connection conn;
	private Statement stmt;
	
	public void init() {
		try {
			Class.forName("org.sqlite.JDBC");
			
			conn = DriverManager.getConnection("jdbc:sqlite:gim.db");
			conn.setAutoCommit(true);
			
			stmt = conn.createStatement();
			
			String sql = "CREATE TABLE IF NOT EXISTS `message` (" +
			  "`id` INTEGER PRIMARY KEY AUTOINCREMENT, " +
			  "`seq` INTEGER, " +
			  "`sender` INTEGER," +
			  "`receiver` INTEGER," +
			  "`gid` INTEGER," +
			  "`type` INTEGER," +
			  "`timestamp` INTEGER," +
			  "`msgid` INTEGER," +
			  "`content` TEXT," +
			  "`extra` TEXT," +
			  "`sess` TEXT," +
			  "`status` INTEGER" +
			")";
			
			stmt.executeUpdate(sql);
			
			sql = "CREATE INDEX IF NOT EXISTS idx_seq ON `message` (`seq`)";
			stmt.executeUpdate(sql);
			
			sql = "CREATE INDEX IF NOT EXISTS idx_sess ON `message` (`sess`)";
			stmt.executeUpdate(sql);
			
			sql = "CREATE INDEX IF NOT EXISTS idx_timestamp ON `message` (`timestamp`)";
			stmt.executeUpdate(sql);
		}catch(Exception e){
			e.printStackTrace();
		}
	}
	
	public void insertMessage(IMMessage im) {
		
		//普通用户跟用户聊天生成session
		String session = CommonUtil.getSession( im.receiver, im.sender);
		
		String sql = "INSERT INTO `message` (seq, sender, receiver, type, timestamp, msgid, " +
				"content, extra, sess, status) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setLong(1, im.seq);
			ps.setLong(2, im.sender);
			ps.setLong(3, im.receiver);
			ps.setInt(4, 0);
			ps.setInt(5, (int) (System.currentTimeMillis() / 1000));
			ps.setLong(6, im.msgid);
			ps.setString(7, im.content);
			ps.setString(8, im.extra);
			ps.setString(9, session);
			ps.setInt(10, im.status);
			ps.addBatch();
			ps.executeBatch();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public void insertGroupMessage(IMMessage im) {
		//群组聊天生成session
		String session = CommonUtil.getGroupSession(im.receiver, im.gid);
		
		String sql = "INSERT INTO `message` (seq, sender, receiver, type, timestamp, msgid, " +
				"content, extra, sess, status) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setLong(1, im.seq);
			ps.setLong(2, im.sender);
			ps.setLong(3, im.receiver);
			ps.setInt(4, 1);
			ps.setInt(5, (int) (System.currentTimeMillis() / 1000));
			ps.setLong(6, im.msgid);
			ps.setString(7, im.content);
			ps.setString(8, im.extra);
			ps.setString(9, session);
			ps.setInt(10, im.status);
			ps.addBatch();
			ps.executeBatch();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public ArrayList<IMMessage> getSessionMsgs(String session, int lastId, int limit) {
		ArrayList<IMMessage> msgs = new ArrayList<IMMessage>();
		try {
			Statement stat = conn.createStatement();
			String sql = "SELECT * FROM `message` WHERE sess='" + session +
					"' AND id>" + lastId + " ORDER BY id DESC LIMIT " + limit;
//			String sql = "SELECT * FROM `message`";
			
			ResultSet rs = stat.executeQuery(sql);
			while (rs.next()) {
				IMMessage im = new IMMessage();
				im.id = rs.getLong("id");
				im.seq = rs.getLong("seq");
				im.sender = rs.getLong("sender");
				im.receiver = rs.getLong("receiver");
				im.type = rs.getInt("type");
				im.timestamp = rs.getInt("timestamp");
				im.msgid = rs.getLong("msgid");
				im.content = rs.getString("content");
				im.extra = rs.getString("extra");
				im.status = rs.getInt("status");
				String sess = rs.getString("sess");
				System.out.println(sess);
				msgs.add(im);
			}	
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return msgs;
	}
	
	public void markMsgSendSuccess(long seq) {
		try {
			String sql = "UPDATE `message` SET status=1 WHERE seq=" + seq;
			stmt.executeUpdate(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public void close() {
		try {
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}
